Import stock opening balances

Find this screen

Open: Import Records > Stock and Prices > Import Opening Balances

How to

Create an import file for stock opening balances

You can import stock opening balances using a CSV Comma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. file.

1. Download the import information and example files:

   • Stock Opening Balance import format (XLS): Details of the information you need to include in the file.
   • Stock Opening Balance example CSV file: Use this to import from CSV format.
2. Create your import file in the format of the CSV example file.

3. Read the information in the import format file (XLS), and make sure your information is correct and all mandatory fields are included.

   Note: Each opening balance must have a Item, Warehouse, Bin, Quantity and Cost.

4. Save your import file.

Validate your import file

You can choose to validate the import file, which checks that the records are in the correct format before you import. Doing this doesn't import any information.

1. Select Validate records only and click OK.

2. Browse to the import file you created, select it and then click Open.

   Sage 200 checks the file. If there are invalid records, the reason for the failure is shown in the error log.

3. Click Print Error Log to see why the record(s) are not valid.
4. Make any corrections required to the records in your import file.
5. To re-validate your import file, repeat this process.

Import stock opening balances

1. Select Validate and import records.

2. Choose whether you want to create bins for stock items, if the bins are in the import file but not in Sage 200. To do this, select Create Bins automatically if they have not already been set up.

3. Click OK.

4. Browse to the import file you created, select it and then click Open.

   Sage 200 checks the records and imports the valid ones.

5. If you have invalid records, click Print Error Log to see the reasons. Make required corrections to the records in your import file.

   Tip: We recommend that you remove those records that were successfully imported. As they can't be imported a second time, this stops them cluttering up your report.

6. Repeat until all records are successfully imported.

Note: The import may take a long time to complete if you have a large number of items in the import file. For example, it might take 1 minute per 1,000 items in the import file. Therefore, it's a good idea to remove any unnecessary items from the import file. So if you're only updating 10 items in a file that has 10,000 items, then edit the import file so it only contains those 10 items.

---

Useful info

Importing opening balances

Opening balances update the quantity in stock and the buying (cost) price (used to value your stock). You enter the balance for each warehouse and bin (if the item's stored in more than one).

You can only import an opening balance once per stock item per warehouse or bin.

The stock item must already exist and be stored in the specified warehouse(s). If you have opening balances for stock items that are stored in bins that you haven't yet created in Sage 200, you can choose to allow Sage 200 to create the bins from the import file.

Note: You cannot change the date of an imported opening balance transaction. The opening balance transaction takes the date from your PC at the time of import.

---

What happens when

What happens when I validate the file?

Sage 200 checks for the following:

• Whether an opening balance has already been entered for the item at the warehouse / bin specified.
• Whether the stock item's warehouse / bin is correct.
• Whether the stock item code is correct.
• All fields are correctly formatted.

What happens when I import the file?

• An opening balance transaction is added to the stock history.

  Note: Your nominal accounts are not updated.

• The quantity in stock is updated.
• The cost price is updated.
• The opening balance transaction takes the date from your PC during import.